--POUR CONTROLE


define erp='erp_mli2.';
define outils='outils_mli2.';

 --CALL uvse_ExistsOrCreate('ART_GENECHAP');

 DELETE FROM se_ART_GENECHAP;
/*
select * from (
SELECT &outils.stragg(source) source,gene_cod, gene_ind, chap_cod, enreg_id, enreg_s, genechap_rang, genechap_lib, genechap_lib2
FROM (
  SELECT 'uv' source ,g.* FROM se_ART_GENECHAP g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.ART_GENECHAP g
  )

GROUP BY gene_cod, gene_ind, chap_cod, enreg_id, enreg_s, genechap_rang, genechap_lib, genechap_lib2
HAVING Count(1)!=2 ) r
ORDER BY gene_cod,gene_ind,genechap_rang*/

 INSERT INTO se_ART_GENECHAP
 (SELECT gene_cod,
         gene_ind,
         'MLI' chap_cod,
         0 enreg_id,
         'N' enreg_s,
         1 genechap_rang,
         '' genechap_lib,
         '' genechap_lib2
    FROM se_art_gene g
    WHERE g.prfgene_cod LIKE 'VRT%'
     );

 UPDATE se_ART_GENECHAP tf SET enreg_id=(select enreg_id FROM &erp.ART_GENECHAP f WHERE f.gene_cod=tf.gene_cod AND f.gene_ind=tf.gene_ind AND f.chap_cod=tf.chap_cod ) ;
 UPDATE se_ART_GENECHAP tf SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;



 commit;